package com.ruoyi.web.service;


import com.alibaba.druid.pool.DruidDataSource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;

@Service
public class C2IntegrationFailedDistService {

    @Autowired
    private DruidDataSource druidDataSource;

    public Map<String, Integer> getResponsiblePartyCount() {
        // 构造 SQL 查询语句
        String sql = "SELECT `responsibleParty`, COUNT(*) AS `count` " +
                "FROM `integration_fail_dts` " +
                "GROUP BY `responsibleParty`;";

        System.out.println("Executing SQL: " + sql);

        // 存储统计结果
        Map<String, Integer> statistics = new HashMap<>();

        try (Connection conn = druidDataSource.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            // 执行查询并处理结果
            try (ResultSet rs = stmt.executeQuery()) {
                while (rs.next()) {
                    String responsibleParty = rs.getString("responsibleParty");
                    int count = rs.getInt("count");

                    // 将结果存储在 map 中
                    statistics.put(responsibleParty, count);
                }
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }

        return statistics;
    }


    /*
     * 集成组版失败问题分布
     * 获取当前周的数据
     */
    public Map<String, Integer> getCurrentWeekResponsiblePartyCount() {
        // 构造 SQL 查询语句，使用时间字段 `time`
        String sql = "SELECT `responsibleParty`, COUNT(*) AS `count` " +
                "FROM `integration_fail_dts` " +
                "WHERE YEARWEEK(`time`, 1) = YEARWEEK(CURRENT_DATE(), 1) " +
                "GROUP BY `responsibleParty`;";

        System.out.println("Executing SQL: " + sql);

        // 存储统计结果
        Map<String, Integer> statistics = new HashMap<>();

        try (Connection conn = druidDataSource.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            // 执行查询并处理结果
            try (ResultSet rs = stmt.executeQuery()) {
                while (rs.next()) {
                    String responsibleParty = rs.getString("responsibleParty");
                    int count = rs.getInt("count");

                    // 将结果存储在 map 中
                    statistics.put(responsibleParty, count);
                }
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }

        return statistics;
    }


    /*
     * 集成组版失败问题分布
     * 获取当前自然月的数据
     */
    public Map<String, Integer> getCurrentMonthResponsiblePartyCount() {
        // 构造 SQL 查询语句，使用时间字段 `time`
        String sql = "SELECT `responsibleParty`, COUNT(*) AS `count` " +
                "FROM `integration_fail_dts` " +
                "WHERE YEAR(`time`) = YEAR(CURRENT_DATE()) " +
                "AND MONTH(`time`) = MONTH(CURRENT_DATE()) " +
                "GROUP BY `responsibleParty`;";

        System.out.println("Executing SQL: " + sql);

        // 存储统计结果
        Map<String, Integer> statistics = new HashMap<>();

        try (Connection conn = druidDataSource.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            // 执行查询并处理结果
            try (ResultSet rs = stmt.executeQuery()) {
                while (rs.next()) {
                    String responsibleParty = rs.getString("responsibleParty");
                    int count = rs.getInt("count");

                    // 将结果存储在 map 中
                    statistics.put(responsibleParty, count);
                }
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }

        return statistics;
    }

    /*
     * 集成组版失败问题分布
     * 获取当前自然季度的数据
     */
    public Map<String, Integer> getCurrentQuarterResponsiblePartyCount() {
        // 构造 SQL 查询语句，使用时间字段 `time`
        String sql = "SELECT `responsibleParty`, COUNT(*) AS `count` " +
                "FROM `integration_fail_dts` " +
                "WHERE YEAR(`time`) = YEAR(CURRENT_DATE()) " +
                "AND QUARTER(`time`) = QUARTER(CURRENT_DATE()) " +
                "GROUP BY `responsibleParty`;";

        System.out.println("Executing SQL: " + sql);

        // 存储统计结果
        Map<String, Integer> statistics = new HashMap<>();

        try (Connection conn = druidDataSource.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            // 执行查询并处理结果
            try (ResultSet rs = stmt.executeQuery()) {
                while (rs.next()) {
                    String responsibleParty = rs.getString("responsibleParty");
                    int count = rs.getInt("count");

                    // 将结果存储在 map 中
                    statistics.put(responsibleParty, count);
                }
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }

        return statistics;
    }

    /*
     * 集成组版失败问题分布
     * 获取当前年度的数据
     */
    public Map<String, Integer> getCurrentYearResponsiblePartyCount() {
        // 构造 SQL 查询语句，使用时间字段 `time`
        String sql = "SELECT `responsibleParty`, COUNT(*) AS `count` " +
                "FROM `integration_fail_dts` " +
                "WHERE YEAR(`time`) = YEAR(CURRENT_DATE()) " +
                "GROUP BY `responsibleParty`;";

        System.out.println("Executing SQL: " + sql);

        // 存储统计结果
        Map<String, Integer> statistics = new HashMap<>();

        try (Connection conn = druidDataSource.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            // 执行查询并处理结果
            try (ResultSet rs = stmt.executeQuery()) {
                while (rs.next()) {
                    String responsibleParty = rs.getString("responsibleParty");
                    int count = rs.getInt("count");

                    // 将结果存储在 map 中
                    statistics.put(responsibleParty, count);
                }
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }

        return statistics;
    }
}
